if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[supportDeleteCR]') and 
  OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[supportDeleteCR]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO      

/******************************************************************  
* Name:				supportDeleteCR  
* Purpose:			remove CR from Insight database - used by Insight Support  
*  
* PARAMETERS  
* Name				Description       
* -------------		-------------------------------------------  
* @protocolNumber   protocol Number string - e.g. '2003P000606'
* @processName		Continuing Review Name - e.g. 'CR8'    
*
--------------------
exec [dbo].[supportDeleteCR]  
@protocolNumber ='2003p000606'
,@processName ='CR8'
--------------------
* MODIFICATIONS:
* -------------------------------------------------------------
* 11-12-2009		Created jml29
******************************************************************/  
CREATE PROCEDURE [dbo].[supportDeleteCR]  
(  
@protocolNumber varchar(12)
,@processName varchar(50)
,@protId int=null
)  
  
AS BEGIN  
  
--SET NOCOUNT ON  
  
BEGIN TRANSACTION supportDeleteCR

declare
@protocolId int
,@protocolProcessId int

--select * from protocol (nolock) where protocolNumber = '2003p000606' and IsCurrent=1
--select * from protocolprocess (nolock) where protocolId=125159 and name like 'cr%'

if @protId=null
  select @protocolId = (select id from protocol where protocolNumber = @protocolNumber and IsCurrent=1 )
else
  select @protocolId = @protId

select @protocolProcessId = (
  select id from protocolProcess pp where name = @processName and protocolId = (
    select id from protocol where protocolNumber = @protocolNumber and IsCurrent=1 ) )

--select * from dbo.PendingApplications_Fct where ProtocolProcessId = @protocolProcessId
delete dbo.PendingApplications_Fct where ProtocolProcessId = @protocolProcessId

-- if error in deleting the record raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END

--select * from dbo.ProtocolBoardReview (nolock) where protocolSubmissionid = (
delete dbo.ProtocolBoardReview where protocolSubmissionid = (
  select id from protocolSubmission where protocolId = @protocolId 
    and FormsID = @processName )

-- if error in deleting the record raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END


--select * from protocolSubmission where protocolId = @protocolId
delete protocolSubmission where protocolId = @protocolId
  and FormsID = @processName

-- if error in deleting the record raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END

--select * from dbo.ProtocolProcess_WorkFlow_Map (nolock)where protocolProcessId = @protocolProcessId
delete dbo.ProtocolProcess_WorkFlow_Map where protocolProcessId = @protocolProcessId

-- if error in deleting the record raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END

--select * from ProtocolDocument WHERE AddedPPId = @protocolProcessId
delete ProtocolDocument WHERE AddedPPId = @protocolProcessId

-- if error in deleting the record raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END
  
--select * from dbo.ProtocolVersion_QuestionnaireAnswer_Map where protocolVersionId in (
delete dbo.ProtocolVersion_QuestionnaireAnswer_Map where protocolVersionId in (
select id from ProtocolVersion WHERE ProtocolProcessId = @protocolProcessId )

--select * from ProtocolVersion WHERE ProtocolProcessId = @protocolProcessId
DELETE ProtocolVersion WHERE ProtocolProcessId = @protocolProcessId

-- if error in deleting the record raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END

exec [dbo].[DeleteProtocolProcess] @id = @protocolProcessId

-- if error in exec raise error
 IF @@ERROR <> 0 
 BEGIN
	GOTO ErrorHandler
 END
--------------------------
--now disable Workflow Email Notifications:
update swActivityList set Status='completed' where ExecutionID in (
select ExecutionId from ProtocolProcess_WorkFlow_Map where protocolProcessId = @protocolProcessId
 )  and status='awaiting'

update swExecute set InternalStatus='FN' where id in (
select ExecutionId from ProtocolProcess_WorkFlow_Map where protocolProcessId = @protocolProcessId
 )

update swExecutionDetails set Status='Z' where Executionid in (
select ExecutionId from ProtocolProcess_WorkFlow_Map where protocolProcessId = @protocolProcessId
 )  and status !='Z'
--------------------------

COMMIT TRANSACTION supportDeleteCR
RETURN 0

-- check for failure
ErrorHandler:
BEGIN
	ROLLBACK TRANSACTION supportDeleteCR
	RAISERROR('Errors in Deleting CR. ', 16, 1)

	RETURN 1
END
   
SET NOCOUNT OFF
END
